package com.yc.jdbc.d1216;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.function.Function;

//  Jdbc 模板类
public class JdbcTemplate {

    public static void main(String[] args) {
        JdbcTemplate.update("update dept set loc=? where deptno=?","纽约", 10);

        System.out.println("---------------------queryList----------------------");
        List<Map<String, Object>> list1 = JdbcTemplate.queryList("select * from emp");
        // 集合遍历 + 打印元素
        list1.forEach(System.out::println);

        List<Map<String, Object>> list2 = JdbcTemplate.queryList("select * from dept");
        list2.forEach(System.out::println);

        System.out.println("---------------------queryValue----------------------");
        // 返回的统计值是 BigDecimal ? , BigDecimal 没有最大值限制 表示任意大小的数值
        BigDecimal cnt = JdbcTemplate.queryValue("select count(*) from emp");
        int intCnt = cnt.intValue();
        System.out.println("intCnt = " + intCnt);
        // 必须使用与数据库返回值兼容的类型
        Timestamp date = JdbcTemplate.queryValue("select sysdate from dual");
        System.out.println("当前时间 Timestamp = " + date);

        System.out.println("---------------------queryOne----------------------");
        Map<String, Object> one = JdbcTemplate.queryOne("select * from emp where empno = 7521");
        System.out.println("one = " + one);
        try{
            JdbcTemplate.queryOne("select * from emp where job = 'MANAGER'");
        }catch (Exception e){
            System.out.println("e.getMessage() = " + e.getMessage());
        }

        System.out.println("---------------------queryPage-全部---------------------");
        String sql = "select * from emp";
        List<Map<String, Object>> list = JdbcTemplate.queryList(sql);
        list.forEach(System.out::println);  // 遍历打印集合元素
        System.out.println("---------------------queryPage-每页5行,查第2页---------------------");
        Page page = JdbcTemplate.queryPage(sql, 2, 5);
        System.out.println("page.total = " + page.total);
        page.data.forEach(System.out::println);
        System.out.println("---------------------queryPage-每页8行,查第2页---------------------");
        page = JdbcTemplate.queryPage(sql, 2, 8);
        System.out.println("page.total = " + page.total);
        page.data.forEach(System.out::println);


    }

    private static Connection getConnection() throws SQLException {
        String url = "jdbc:oracle:thin:@localhost:1521:helowin";
        String username = "scott";
        String password = "a";
        return DriverManager.getConnection(url, username, password);
    }

    private static PreparedStatement buildPreparedStatement(Connection conn, String sql, Object...args) throws SQLException {
        System.out.println("语句: " + sql);
        System.out.println("参数: " + Arrays.toString(args));
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            statement.setObject(i+1, args[i]);
        }
        return statement;
    }

    // 执行增删改的函数, 该方法不是模板方法, 简单的执行语句, 没有对结果的处理
    // Object...args 是可变参数数组 => 数组
    public static int update (String sql, Object...args){
        // 获取连接对象
        // 自动关闭连接, 注意: 关闭连接时, 也会同时关闭语句和结果集
        try(Connection conn = getConnection()){
            // 构建预编译语句对象
            PreparedStatement stat = buildPreparedStatement(conn, sql, args);
            // 执行 executeUpdate 执行所有非查询的语句, insert,update,delete,create..
            return stat.executeUpdate();
        } catch (SQLException e){
            throw new RuntimeException("执行SQL失败!", e);
        }
    }

    // 执行查询的函数, 该方法是模板方法, 有对结果处理, TODO 交给接口对完成
    public static <T> T query (String sql, Function<ResultSet,T> handler, Object...args){
        try(Connection conn = getConnection()){
            // 构建预编译语句对象
            PreparedStatement stat = buildPreparedStatement(conn, sql, args);
            ResultSet rs = stat.executeQuery();
            // 函数编程
            T t = handler.apply(rs);
            return t;
        } catch (SQLException e){
            throw new RuntimeException("执行SQL失败!", e);
        }
    }

    // 查询某个表所有的记录,获取多行记录(0~n行)  select * from emp
    // public static List<Map<String,Object>> query(String sql){return null;}
    // 随机组合条件查询,获取多行记录(0~n行)  select * from emp ename = ? and job = ? , select * from emp hiredate=? and sal=? ...
    public static List<Map<String,Object>> queryList(String sql, Object...args){
        Object data = query(sql, new Function<ResultSet, Object>() {
            @Override
            public Object apply(ResultSet rs) {
                List<Map<String, Object>> ret = new ArrayList<>();
                try {
                    // 获取结果集元数据对象(对结果集的描述(列名,列数...))
                    ResultSetMetaData metaData = rs.getMetaData();
                    while (rs.next()) {
                        // 读取到一行数据,
                        Map<String, Object> row = new LinkedHashMap<>();
                        // 如何获取字段名
                        int columnCount = metaData.getColumnCount(); // 列数
                        // 将该行数据存入 map 集合
                        for (int i = 0; i < columnCount; i++) {
                            String columnName = metaData.getColumnName(i + 1); // 列名
                            Object columnValue = rs.getObject(columnName);     // 列值
                            row.put(columnName, columnValue);
                        }
                        // 将 map 集合存入 list 中
                        ret.add(row);
                    }
                } catch (SQLException e) {
                    throw new RuntimeException("遍历结果集错误!", e);
                }
                return ret;
            }
        }, args);
        List<Map<String,Object>> list = (List<Map<String, Object>>) data;
        return list;
    }
    // 统计查询,获取一个数值  select count(*) from emp where deptno = ?
    public static <T> T queryValue(String sql, Object...args){
        return (T) query(sql, rs -> {
                try {
                    rs.next();
                    return rs.getObject(1);
                } catch (SQLException e) {
                    throw new RuntimeException("遍历结果集错误!", e);
                }
            }
        , args);
    }

    // 查询一个员工(以主键为条件, 结果为0~1行)  select * from emp where empno = ?
    public static Map<String,Object> queryOne(String sql, Object...args){
        List<Map<String, Object>> list = queryList(sql, args);
        if(list.isEmpty()){
            return null;
        } else if (list.size() > 1){
            throw new RuntimeException(String.format("查询结果应该是1行, 实际结果是%s行!", list.size()));
        } else {
            return list.get(0);
        }
//        return (Map<String,Object>) query(sql, rs -> {
//                    List<Map<String, Object>> list = queryList(sql, args);
//                    if(list.isEmpty()){
//                        return null;
//                    } else if (list.size() > 1){
//                        throw new RuntimeException(String.format("查询结果应该是1行, 实际结果是%s行!", list.size()));
//                    } else {
//                        return list.get(0);
//                    }
//                }
//                , args);
    }

    // 分页查询, 参数:第几页,每页几行, 返回结果是查询页的数据以及总行数(才能计算总页数)  ...
    public static Page queryPage(String sql, int pageNum, int pageSize, Object...args){
        /*
             查询当前页  queryList
             select a.* from ( select a.*, rownum rn from ( sql ) a rownum <= 结束行 ) a where rn >= 开始行
             查询总行数  queryValue
             select count(*) from ( sql ) a
             计算起止行
             begin  = (pageNum - 1) * pageSize +1
             end    =  pageNum * pageSize
         */
        int begin = (pageNum - 1) * pageSize + 1;
        int end   = pageNum * pageSize;

        String dataSql =
                "select a.* from ( select a.*, rownum rn from ( " + sql + " ) a\n" +
                "                   where rownum <= "+end+" ) a\n" +
                "          where rn >= " + begin;
        String totalSql= "select count(*) from ( " + sql + " ) a";

        List<Map<String, Object>> data = queryList(dataSql, args);
        BigDecimal total = queryValue(totalSql, args);

        Page page = new Page(data, total.longValue());

        return page;
    }

    // 分页结果对象
    public static class Page {
        // 查询页的数据
        List<Map<String,Object>> data;
        // 总行数
        long total;

        public Page(List<Map<String, Object>> data, long total) {
            this.data = data;
            this.total = total;
        }

        public List<Map<String, Object>> getData() {
            return data;
        }

        public void setData(List<Map<String, Object>> data) {
            this.data = data;
        }

        public long getTotal() {
            return total;
        }

        public void setTotal(long total) {
            this.total = total;
        }
    }

}

